﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MVCSiteJDG.Dao.DataAccess
{
    public class Creator
    {
        //public static void createApplication(string Id)
        //{
        //    try
        //    {
        //        System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
        //        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO APPLICATION VALUES " +  +","+ + );

        //        connection.Open();
        //        command.ExecuteNonQuery();
        //    }
        //    catch (Exception)
        //    {

        //    }

        //}
        public static void createAccount(string accountName, string accountPassword, string accountRegDate, string accountLastLogin, string accountEmail, string accountContactNumber, string accountBalance, string costCentreID)
        {
            //try
            //{
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO Account (accountName, accountPassword, accountRegDate, accountLastLogin, accountEmail, accountContractNumber, accountBalance, costCentreID1) VALUES ( '" + accountName + "','" + accountPassword + "','" + accountRegDate + "','" + accountLastLogin + "','" + accountEmail + "','" + accountContactNumber + "','" + accountBalance + "'," + costCentreID + ")", connection);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createAsset(string departmentID, string subCategoryID, string productID)
        {
            //try
            //{
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO ASSET (departmentID, subCategoryID, productID) VALUES (" + departmentID + "," + subCategoryID + "," + productID + ")", connection);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createCartItem(string productID, string cartID)
        {
            //try
            //{
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO CARTITEM (productID, cartID) VALUES (" + productID + "," + cartID + ")", connection);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            //}
            //catch (Exception)
            //{

            //}

        }
        public static void createCatalog(string catName, string catDescription, string catImage)
        {
            //try
            //{
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO CATALOG (catName, catDescription, catImage) VALUES ('" + catName + "','" + catDescription + "','" + catImage + "')", connection);
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        //public static void createCatalogProduct(string prodId, string catId)
        //{
        //    try
        //    {
        //        System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
        //        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO CATALOGPRODUCT VALUES " +  +","+ + );

        //        connection.Open();
        //        command.ExecuteNonQuery();
        //    }
        //    catch (Exception)
        //    {

        //    }
        //}
        public static void createCostCentre(string totalValue)
        {
            //try
            //{
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO COSTCENTRE (totalValue) VALUES ('" + totalValue + "')", connection);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createDepartment(string building, string floor, string room, string costCentreID)
        {
            //try
            //{
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO DEPARTMENT (building, floor, room, costCentreID) VALUES ('" + building + "','" + floor + "','" + room + "'," + costCentreID + ")", connection);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        //public static void createEmployee(string Id, string details)
        //{
        //    try
        //    {
        //        System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
        //        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO EMPLOYEE VALUES " +  +","+ + );

        //        connection.Open();
        //        command.ExecuteNonQuery();
        //    }
        //    catch (Exception)
        //    {

        //    }
        //}
        public static bool createLog(string requestID, string message)
        {
            try
            {
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO LOG (requestID, message) VALUES (" + requestID + ",'" + message + "')", connection);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            return true;
            }
            catch (Exception)
            {
            return false;
            }
        }
        public static void createMainCategory(string description)
        {
            //try
            //{
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO MAINCATEGORY (description) VALUES ('" + description + "')", connection);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static bool createOrders(string orderStatus, string orderTotalCost, string departmentID)
        {
            try
            {
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO ORDERS (orderStatus, orderTotalCost, departmentID) VALUES ('" + orderStatus + "','" + orderTotalCost + "'," + departmentID + ")", connection);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public static void createOrderLineItem(string productID, string productQuantity, string orderID)
        {
            //try
            //{
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO ORDERLINEITEM (productID, productQuantity, orderID) VALUES (" + productID + ",'" + productQuantity + "'," + orderID + ")", connection);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        //public static void createOrderStatus(string Id, string description)
        //{
        //    try
        //    {
        //        System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
        //        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO ORDERSTATUS VALUES " +  +","+ + );

        //        connection.Open();
        //        command.ExecuteNonQuery();
        //    }
        //    catch (Exception)
        //    {

        //    }
        //}
        public static void createProduct(string productName, string productDescription, string productPrice, string productImage, string productInDate, string catID)
        {
            //try
            //{
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO PRODUCT (productName, productDescription, productPrice,productImage, productInDate, catID) VALUES ('" + productName + "','" + productDescription + "','" + productPrice + "','" + productImage + "','" + productInDate + "'," + catID + ")", connection);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createRequest(string accountID, string technicianID, string requestMessage)
        {
            //try
            //{
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO REQUEST (accountID, technicianID, requestMessage) VALUES (" + accountID + "," + technicianID + ",'" + requestMessage + "')", connection);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createReview(string review, string productID, string accountID)
        {
            //try
            //{
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO REVIEW (review, productID, accountID) VALUES ('" + review + "'," + productID + "," + accountID + ")", connection);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createShoppingCart(string shoppingDate, string accountID)
        {
            //try
            //{
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO ShoppingCart (shoppingDate, accountID) VALUES ('" + shoppingDate + "'," + accountID + ")", connection);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createShoppingHistory(string accountID, string orderID, string totalValue)
        {
            //try
            //{
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO ShoppingHistory (accountID, orderID, totalValue) VALUES (" + accountID + "," + orderID + ",'" + totalValue + "')", connection);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createSubCategory(string mainCategoryID, string description)
        {
            //try
            //{
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO SUBCATEGORY (mainCategoryID, description) VALUES (" + mainCategoryID + ",'" + description + "')",connection);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createSupplier(string supplierName)
        {
            //try
            //{
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO SUPPLIER (supplierName) VALUES ('" + supplierName + "')", connection);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
        public static void createTechnician(string technicianName)
        {
            //try
            //{
                System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
                System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand("INSERT INTO TECHNICIAN (technicianName) VALUES ('" + technicianName + "')", connection);

                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            //}
            //catch (Exception)
            //{

            //}
        }
    }
}